package com.qf.studentmanager.dao;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.qf.studentmanager.model.Clazz;
import com.qf.studentmanager.model.Course;
import com.qf.studentmanager.model.Exam;
import com.qf.studentmanager.model.Grade;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ExamDao {
    ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
    QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);
    public Long getTotal(String gid, String cid) {
        StringBuffer stringBuffer = new StringBuffer("select count(1) from exam e ");
        if (gid != null && cid == null){
            stringBuffer.append("where e.gid = ?");
        }else if (gid != null && cid != null){
            stringBuffer.append("where e.gid = ? and e.cid = ?");
        }

        try {
            if (gid != null && cid == null){
                return  queryRunner.query(stringBuffer.toString(), new ScalarHandler<>() , gid);
            }else if (gid != null && cid != null){
                return  queryRunner.query(stringBuffer.toString(), new ScalarHandler<>(),gid , cid);
            }else {
                return queryRunner.query(stringBuffer.toString(), new ScalarHandler<>());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0L;
    }


    /*select e.*, g.gradeName , cr.courseName from exam e left join clazz c on e.cid = c.cid left join grade g on e.gid = g.gid left join course cr on e.crid = cr.cid where e.gid = ?
    select  e.*, g.gradeName , cr.courseName from exam e left join clazz c on e.cid = c.cid left join grade g on e.gid = g.gid left join course cr on e.crid = cr.cid where e.cid = ?
    select  e.*, g.gradeName , cr.courseName from exam e left join clazz c on e.cid = c.cid left join grade g on e.gid = g.gid left join course cr on e.crid = cr.cid  */
    public List<Exam> getExamByPage(int p, int r, String sort, String order, String gid, String cid) {
        String sql = null;
        //准备好sql
        if (gid == null && cid != null){
            sql = "    select  e.*, g.gradeName , cr.courseName , c.clazzName from exam e left join clazz c on e.cid = c.cid left join grade g on e.gid = g.gid left join course cr on e.crid = cr.cid where e.cid = ?   order by  "+ sort + " " + order+"   limit ? , ?; ";
        }else if (gid != null && cid == null){
            sql = "select e.*, g.gradeName , cr.courseName , c.clazzName from exam e left join clazz c on e.cid = c.cid left join grade g on e.gid = g.gid left join course cr on e.crid = cr.cid where e.gid = ?   order by  "+ sort + " " + order+"   limit ? , ?; " ;
        }else {
            sql = "select  e.*, g.gradeName , cr.courseName , c.clazzName from exam e left join clazz c on e.cid = c.cid left join grade g on e.gid = g.gid left join course cr on e.crid = cr.cid    order  by "+ sort + " " + order+"  limit ? , ?;";
        }

        try {
            ResultSetHandler<List<Exam>> rsh = new ResultSetHandler<List<Exam>>() {
                @Override
                public List<Exam> handle(ResultSet resultSet) throws SQLException {
                    List<Exam> list = new ArrayList<>();
                    while (resultSet.next()){
                        int eid = resultSet.getInt("eid");
                        String examName = resultSet.getString("examName");
                        Date examDate = resultSet.getDate("examDate");
                        String examType = resultSet.getString("examType");
                        int gid1 = resultSet.getInt("gid");
                        int cid1 = resultSet.getInt("cid");
                        int crid = resultSet.getInt("crid");
                        String remark = resultSet.getString("remark");

                        String gradeName = resultSet.getString("gradeName");
                        Grade grade = new Grade();
                        grade.setGradeName(gradeName);
                        String courseName = resultSet.getString("courseName");
                        Course course = new Course();
                        course.setCourseName(courseName);
                        String clazzName = resultSet.getString("clazzName");
                        Clazz clazz = new Clazz();
                        clazz.setClazzName(clazzName);

                       Exam exam = new Exam(eid,examName,examDate,examType,gid1,cid1,crid,remark,grade,clazz,course);

                       list.add(exam);
                    }
                    return list;
                }
            };

            if (gid == null && cid != null){
                return  queryRunner.query(sql, rsh ,cid , p , r ) ;
            }else if (gid != null && cid == null){
                return queryRunner.query(sql, rsh ,gid  , p , r ) ;
            }else {
                return queryRunner.query(sql, rsh  , p , r ) ;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    public Integer addExam(Exam exam) throws SQLException {
        return queryRunner.update("insert into  exam( examName, examDate, examType, gid, cid, crid, remark) values( ?,?,?,?,?,?,?);",exam.getExamName(),exam.getExamDate(),exam.getExamType(),exam.getGid(),exam.getCid(),exam.getCrid(),exam.getRemark());
    }

    public Integer deleteExamByEid(String eid) throws SQLException {
        return queryRunner.update("delete from exam where eid =?" , eid);
    }
}
